﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sinacor.Infra.Service.Data;
using System.Data.Common;
using System.Data;
using PSE.Communication.Fix.Common;

namespace Sinacor.Communication.Fix.Repository
{
    public class SinacorDataBaseRepository : BaseProvider, IRepositoryProvider
    {
        private int? _maxLinesPerBlock = null;
        public int? MaxLinesPerBlock
        {
            get
            {
                if (!_maxLinesPerBlock.HasValue)
                {
                    if (base.Configuration != null && base.Configuration.Settings != null
                        && base.Configuration.Settings.AllKeys != null)
                    {
                        if (base.Configuration.Settings.AllKeys.Contains<string>("maxLinesPerBlock"))
                        {
                            string value = base.Configuration.Settings["maxLinesPerBlock"].Value;
                            if (!string.IsNullOrEmpty(value))
                                _maxLinesPerBlock = Convert.ToInt32(value);
                            else
                                _maxLinesPerBlock = null;
                        }

                        //se um valor invalido for informado, ignorar
                        if (_maxLinesPerBlock.HasValue && _maxLinesPerBlock.Value <= 0)
                            _maxLinesPerBlock = null;
                    }
                }
                return _maxLinesPerBlock;

                //return 50;
            }
        }

        public SinacorDataBaseRepository()
            : base()
        {

        }

        public SinacorDataBaseRepository(ProviderConfigurationElement configuration)
            : base(configuration)
        {

        }

        public void Save(RepositoryParameter[] parameters)
        {                        
            if (parameters == null)
                throw new ArgumentNullException("parameter");

            StringBuilder sql = new StringBuilder();

            sql.Append("BEGIN ");

            for (int i = 0; i < parameters.Length; i++)
            {
                if (parameters[i].ReceiveDate == null)
                    throw new ArgumentNullException("parameter.ReceiveDate");

                if (parameters[i].Message == null)
                    throw new ArgumentNullException("parameter.Message");

                if (parameters[i].MessageType == null)
                    throw new ArgumentNullException("parameter.MessageType");

                sql.AppendFormat("INSERT INTO TFWMSG_ORDE_RECB (COD_SEQ_MSG, DATA_RECB_MSG, COD_EMP, COD_TIPO_MSG_PFIX, TXT_MSG ) VALUES ( :COD_SEQ_MSG_{0}, :DATA_RECB_MSG_{0}, :COD_EMP_{0}, :COD_TIPO_MSG_PFIX_{0}, :TXT_MSG_{0} );", i.ToString());
            }

            sql.Append(" END;");

            using (OracleDataBase db = new OracleDataBase())
            {
                using (DbCommand cmd = db.GetSqlStringCommand(sql.ToString()))
                {
                    for (int i = 0; i < parameters.Length; i++)
                    {
                        db.AddInParameter(cmd, ":COD_SEQ_MSG_" + i.ToString(), DbType.Int32, parameters[i].Id);
                        db.AddInParameter(cmd, ":DATA_RECB_MSG_" + i.ToString(), DbType.DateTime, parameters[i].ReceiveDate);
                        db.AddInParameter(cmd, ":COD_EMP_" + i.ToString(), DbType.Int32, parameters[i].CompanyId);
                        db.AddInParameter(cmd, ":COD_TIPO_MSG_PFIX_" + i.ToString(), DbType.String, parameters[i].MessageType);
                        db.AddInParameter(cmd, ":TXT_MSG_" + i.ToString(), DbType.String, parameters[i].Message);
                    }

                    db.ExecuteNonQuery(cmd);
                }
            }
        }

        public void Save(RepositoryParameter parameter)
        {
            if (parameter == null)
                throw new ArgumentNullException("parameter");

            if (parameter.ReceiveDate == null)
                throw new ArgumentNullException("parameter.ReceiveDate");

            if (parameter.Message == null)
                throw new ArgumentNullException("parameter.Message");

            if (parameter.MessageType == null)
                throw new ArgumentNullException("parameter.MessageType");

            StringBuilder sql = new StringBuilder();

            sql.Append("INSERT INTO TFWMSG_ORDE_RECB (COD_SEQ_MSG, DATA_RECB_MSG, COD_EMP, COD_TIPO_MSG_PFIX, TXT_MSG ) VALUES ( :COD_SEQ_MSG, :DATA_RECB_MSG, :COD_EMP, :COD_TIPO_MSG_PFIX, :TXT_MSG )");

            using (OracleDataBase db = new OracleDataBase())
            {
                using (DbCommand cmd = db.GetSqlStringCommand(sql.ToString()))
                {
                    db.AddInParameter(cmd, ":COD_SEQ_MSG", DbType.Int32, parameter.Id);
                    db.AddInParameter(cmd, ":DATA_RECB_MSG", DbType.DateTime, parameter.ReceiveDate);
                    db.AddInParameter(cmd, ":COD_EMP", DbType.Int32, parameter.CompanyId);
                    db.AddInParameter(cmd, ":COD_TIPO_MSG_PFIX", DbType.String, parameter.MessageType);
                    db.AddInParameter(cmd, ":TXT_MSG", DbType.String, parameter.Message);

                    db.ExecuteNonQuery(cmd);
                }
            }
        }

        public List<RepositoryMessage> Read(RepositoryParameter parameter)
        {
            if (parameter == null)
                throw new ArgumentNullException("parameter");

            StringBuilder sql = new StringBuilder();
            List<RepositoryMessage> messages = new List<RepositoryMessage>();

            sql.Append("SELECT COD_MSG, COD_SEQ_MSG, DATA_RECB_MSG, COD_EMP, COD_TIPO_MSG_PFIX, TXT_MSG FROM TFWMSG_ORDE_RECB WHERE 1=1 ");

            if (parameter.StartId > 0)
                sql.Append(" AND COD_SEQ_MSG >= :COD_SEQ_MSG");

            if (parameter.ReceiveDate != null && parameter.ReceiveDate != DateTime.MinValue)
                sql.Append(" AND (DATA_RECB_MSG >= :DATA_RECB_MSG_MIN AND DATA_RECB_MSG <= :DATA_RECB_MSG_MAX)");

            if (parameter.CompanyId > 0)
                sql.Append(" AND COD_EMP = :COD_EMP");

            if (!string.IsNullOrEmpty(parameter.MessageType))
                sql.Append(" AND TRIM(COD_TIPO_MSG_PFIX) = :COD_TIPO_MSG_PFIX");

            sql.Append(" ORDER BY COD_SEQ_MSG ");

            if (parameter.Length > 0)
            {
                sql.Insert(0, "SELECT * FROM ( ");
                sql.AppendFormat(" ) WHERE ROWNUM <= {0}", parameter.Length.ToString());
            }

            using (OracleDataBase db = new OracleDataBase())
            {
                using (DbCommand cmd = db.GetSqlStringCommand(sql.ToString()))
                {
                    if (parameter.StartId > 0)
                        db.AddInParameter(cmd, ":COD_SEQ_MSG", DbType.Int64, parameter.StartId);

                    if (parameter.ReceiveDate != null && parameter.ReceiveDate != DateTime.MinValue)
                    {
                        db.AddInParameter(cmd, ":DATA_RECB_MSG_MIN", DbType.DateTime, new DateTime(parameter.ReceiveDate.Year, parameter.ReceiveDate.Month, parameter.ReceiveDate.Day, 0, 0, 0));
                        db.AddInParameter(cmd, ":DATA_RECB_MSG_MAX", DbType.DateTime, new DateTime(parameter.ReceiveDate.Year, parameter.ReceiveDate.Month, parameter.ReceiveDate.Day, 23, 59, 59));
                    }

                    if (parameter.CompanyId > 0)
                        db.AddInParameter(cmd, ":COD_EMP", DbType.Int32, parameter.CompanyId);

                    if (!string.IsNullOrEmpty(parameter.MessageType))
                        db.AddInParameter(cmd, ":COD_TIPO_MSG_PFIX", DbType.String, parameter.MessageType);

                    using (IDataReader reader = db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            RepositoryMessage repositoryMessage = new RepositoryMessage();
                            repositoryMessage.Message = reader.GetValue<string>("TXT_MSG");
                            repositoryMessage.CodMessage = reader.GetValue<long>("COD_SEQ_MSG");
                            messages.Add(repositoryMessage);
                        }
                    }
                }
            }

            return messages;
        }

        public int GetLastMessageNum(int companyId, string messageType)
        {
            if (companyId <= 0)
                throw new ArgumentException(string.Format("Invalid company Id {0}", companyId));

            if (string.IsNullOrEmpty(messageType))
                throw new ArgumentException("Message Type cannot be empty");

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COALESCE(MAX(COD_SEQ_MSG), 0) + 1 LAST_MESSAGE_NUM ");
            sql.Append("FROM TFWMSG_ORDE_RECB ");
            sql.Append("WHERE TRUNC(DATA_RECB_MSG) = TRUNC(SYSDATE) ");
            sql.AppendFormat("  AND COD_TIPO_MSG_PFIX = '{0}' ", messageType);
            sql.AppendFormat("  AND COD_EMP = {0} ", companyId);

            int maxMessageNum = 0;

            using (OracleDataBase db = new OracleDataBase())
            {
                using (DbCommand cmd = db.GetSqlStringCommand(sql.ToString()))
                {
                    using (IDataReader reader = db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                            maxMessageNum = reader.GetValue<int>("LAST_MESSAGE_NUM");
                    }
                }
            }
            return maxMessageNum;
        }
    }
}